<?php

if (!defined('ROOTPATH')) {
	die(-1);
}

require_once INCLUDESPATH . 'db/DBConnection.php';
require_once INCLUDESPATH . 'db/Word.php';

/**
 *
 *
 * @author hutushen222 2010-08-09
 * @version 1.0.0
 */
class WordDAO {

	/**
	 * the default constructor of the word dao
	 *
	 * @author hutushen222
	 */
	public function WordDAO() {

	}

	/**
	 * insert an word's info into the database as a record of the User table.
	 *
	 * @param Word $word -- an word object
	 * @author hutushen222 2010-08-09
	 * @return true -- if successful
	 * @see manipulate
	 */
	public function insert(Word $word) {
		$sql = "insert into `word` (`userid`, `key`, `ps`, `pron`, `acceptation`, `status`, `unit`, `addtime`) " .
		"values ('" .
		$word->getUserid() . "', '" .
		$word->getKey() . "', '" .
		$word->getPS() . "', '" .
		$word->getPron() . "', '" .
		$word->getAcceptation() . "', '" .
		$word->getStatus() . "', '" .
		$word->getUnit() . "', '" .
		$word->getAddTime() . "'".
		")";

		return $this->manipulate($sql);
	}

	/**
	 * update the word's info in the database record
	 *
	 * @param Word $word -- an word object
	 * @author hutushen222 2010-08-09
	 * @return true -- if successful
	 * @see manipulate
	 */
	public function update(Word $word) {
		$sql = "update `word` set " .
		"`key` = '" . $word->getKey() . "', " .
		"`ps` = '" . $word->getPS() . "', " .
		"`pron` = '" . $word->getPron() . "', " .
		"`acceptation` = '" . $word->getAcceptation() . "', " . 
		"`status` = '" . $word->getStatus() . "', " .
		"`unit` = '" . $word->getUnit() . "' " .
		"where `id` = '" . $word->getId() . "'";
		
		return $this->manipulate($sql);
	}

	/**
	 * delete an word from the database whit the given id
	 *
	 * @param int $id -- an id relative with a record in the database
	 * @author hutushen222 2010-08-09
	 * @return true -- if successful
	 * @see manipulate
	 */
	public function delete($id) {
		$sql = "delete from `word` where `id` = " . $id;
		return $this->manipulate($sql);
	}

	/**
	 * get an word object from the database with the given id
	 *
	 * @param int $id -- an id relative with a record in the database
	 * @author hutushen222 2010-08-09
	 * @return Word word -- an word object
	 * @see retrieve
	 */
	public function retrieveById($id) {
		$sql = "select * from `word` where `id` = " . $id;
		$words = $this->retrieve($sql);
		return $words[0];
	}

	/**
	 * get the words from the database with the given property and its value
	 *
	 * @param string $property
	 * @param string $value
	 * @param string $orderby -- a column name of the word table
	 * @author hutushen222 2010-08-09
	 * @return array Words -- an array of word objects
	 * @see retrieve
	 */
	public function retrieveByProperty($property, $value, $orderby = "id", $page = 0, $wordsperpage = 0) {
		$sql = "select * from `word` where " . $property . " = '" . $value .
		"' order by " . $orderby;
		if ($page) {
			$sql .= ' limit ' . ($wordsperpage * ($page - 1)) . ', ' . $wordsperpage;
		}
		return $this->retrieve($sql);
	}

	public function retrieveByPropertyLimited($property, $value, $orderby='id', $order = 1, $offset = 0, $rowcount = 0) {
		# base
		$sql = "select * from `word` where " . $property . " = '" . $value . "'";
		# order
		if ($order == 1) {
			$sql .= ' order by ' . $orderby . ' asc';
		} else {
			$sql .= ' order by ' . $orderby . ' desc';
		}
		# limit
		if (!($offset == 0 && $rowcount == 0)) {
			$sql .= ' limit ' . $offset . ', ' . $rowcount;
		}
		return $this->retrieve($sql);
	}

	/**
	 * get the word objects from the databases with the given properties and
	 * their values
	 *
	 * @param array $properties
	 * @param string $orderby -- a column name of the word table
	 * @param int $order -- 1 => asc 2 => desc
	 * @param int $page -- current page
	 * @param int $wordsperpage -- words number per page will return
	 * @author hutushen222 2010-08-09
	 * @return array Words -- an array of word objects
	 * @see retrieve
	 */
	public function retrieveByPropertiesLimited(array $properties,
	$orderby = "id", $order = 1, $offset = 0, $rowcount = 0) {
		# base
		$sql = "select * from `word` where ";

		foreach($properties as $key => $value) {
			$sql .= '`' . $key . '` = \'' . $value . '\' and ';
		}
		$sql = substr($sql, 0, -4);
		# order
		if ($order == 1) {
			$sql .= ' order by ' . $orderby . ' asc';
		} else {
			$sql .= ' order by ' . $orderby . ' desc';
		}
		# limit
		if (!($offset == 0 && $rowcount == 0)) {
			$sql .= ' limit ' . $offset . ', ' . $rowcount;
		}

		//		echo $sql;
		//		die();

		return $this->retrieve($sql);
	}

	public function retrieveByProperties(array $properties,
	$orderby = "id", $order = 1, $page = 0, $wordsperpage = 21) {
		# manipulate the sql statement
		$sql = "select * from `word` where ";

		foreach($properties as $key => $value) {
			$sql .= '`' . $key . '` = \'' . $value . '\' and ';
		}
		$sql = substr($sql, 0, -4);

		if ($order == 1) {
			$sql .= ' order by ' . $orderby . ' asc';
		} else {
			$sql .= ' order by ' . $orderby . ' desc';
		}

		if ($page) {
			$sql .= ' limit ' . ($wordsperpage * ($page - 1)) . ', ' . $wordsperpage;
		}

		//		echo $sql;
		//		die();

		return $this->retrieve($sql);
	}

	/**
	 * get all the records of word in the database
	 *
	 * @param string $orderby -- a column name of the word table
	 * @author hutushen222 2010-08-09
	 * @return array Words -- an array of word objects
	 * @see retrieve
	 */
	public function retrieveAll($orderby = "id") {
		$sql = "select * from `word` order by " . $orderby;
		return $this->retrieve($sql);
	}

	/**
	 * 统计不同条件下的满足条件的记录的数目
	 *
	 * @param array $params
	 */
	public function statistic($params = array()) {
		$amount = 0;
		$dbc = new DBConnection();
		$dbc->connectAIO();
		$sql = "select count(id) from word";

		if (empty($params)) {
			$result = $dbc->query($sql);
		} else {
			$sql .= ' where ';
			foreach ($params as $key => $value) {
				$sql .= $key . ' = \'' . $value . '\' and ';
			}
			# 删除最后四位字符'and '
			$sql = substr($sql, 0, -4);

			$result = $dbc->query($sql);
		}

		$rows = mysql_fetch_row($result);
		$amount = $rows[0];
			
		mysql_free_result($result);
		$dbc->close();
		return $amount;
	}

	/**
	 * manipulate the dababase for update the info in the table
	 *
	 * For INSERT, UPDATE, DELETE, DROP, etc, mysql_query() returns
	 * TRUE on success or FALSE on error.
	 *
	 * @param string $sql -- a query string
	 * @author hutushen222 2010-08-09
	 * @return true -- if successful
	 */
	private function  manipulate($sql) {
		$dbc = new DBConnection();
		$dbc->connectAIO();
		$dbc->query($sql);
		$dbc->close();
		return true;
	}

	/**
	 * query in the database for the relative records
	 *
	 * For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements
	 * returning resultset, mysql_query() returns a resource on success,
	 * or FALSE on error.
	 *
	 * @param string $sql -- a query string
	 * @author hutushen222 2010-08-09
	 * @return array Words -- an array of word objects
	 */
	private function retrieve($sql) {
		$dbc = new DBConnection();
		$dbc->connectAIO();

		$result = $dbc->query($sql);

		if ($dbc->affectedRows() == 0) {
			return array();
		}

		$words = array();
		while ($row = mysql_fetch_assoc($result)) {
			# initialize a word with the data from the database
			$word = $this->initWord($row['id'], $row['userid'], $row['key'],
			$row['ps'], $row['pron'], $row['acceptation'], $row['status'], $row['unit'], $row['addtime']);

			$words[] = $word;
		}

		mysql_free_result($result);
		return $words;
	}

	/**
	 * initialize a word with these parameters.
	 * you must pay attention on the parameters's order
	 *
	 * @author hutushen222 2010-08-09
	 * @return Word word -- a word object
	 */
	private function initWord() {
		$properties = func_get_args();

		if (func_num_args() < 9) {
			return null;
		}

		$word = new Word();
		$word->setId($properties[0]);
		$word->setUserid($properties[1]);

		$word->setKey($properties[2]);
		$word->setPS($properties[3]);
		$word->setPron($properties[4]);
		$word->setAcceptation($properties[5]);
		$word->setStatus($properties[6]);
		$word->setUnit($properties[7]);
		$word->setAddTime($properties[8]);

		return $word;
	}

}
?>